package util.publish;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TagURL {

	/**
	 * 使用前需要配置parameter表中的getTagurlid
	 * 算法把content、tagurl、wp_term三个表串和在一起链接查询，tagurl做中介wp_term-tag-url-content.url-content-postid
	 * 
	 */
	public int AddTag() {
		
		//final int sourceNet = 6;
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Statement stmtAdd = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");

			conn = DriverManager
					.getConnection("jdbc:mysql://" + host + "/" + dBName + "?user=root");
			stmt = conn.createStatement();
			stmtAdd = conn.createStatement();
			//三个表的连接查询，content表的pageUrl=tagurl表的url，tagurl表的tag= wp_terms表的name
			rs = stmt.executeQuery("select tagurl.id,content.postid,wp_terms.term_id from (content join tagurl on content.pageURL = tagurl.url) " +
					"join wp_terms on tagurl.tag = wp_terms.name where content.postid is not null and pub = 0 and  content.sourceNet = '" + sourceNet + "'" );
			/*rs = stmt.executeQuery("select tagurl.id,content.postid,wp_terms.term_id from (content join tagurl on content.pageURL = tagurl.url) " +
					"join wp_terms on tagurl.tag = wp_terms.name where content.postid is not null and pub = 0");*/
			while (rs.next()) {
				//文章id和tag id都写入wp_term_relationships表
				String sql = "insert into wp_term_relationships values (" + rs.getInt("postid") + "," + rs.getInt("term_id") + ",0,0)";
				stmtAdd.executeUpdate(sql);
				sql = "update tagurl set pub = 1 where id = " + rs.getInt("tagurl.id");
				stmtAdd.executeUpdate(sql);
				System.out.println(rs.getInt("tagurl.id") + "对应的文章——标签关系生成实际tag" + "-----" + dBName);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} finally {
			try {
				if(rs != null) {
					rs.close();
					rs = null;
				}
				if(stmt != null) {
					stmt.close();
					stmt = null;
				}
				if(conn != null) {
					conn.close();
					conn = null;
				}
				if(stmtAdd != null) {
					stmtAdd.close();
					stmtAdd = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 16;

	}
	String dBName = null;
	String host = null;
	String sourceNet = null;
	public void setSourceNet(String sourceNet) {
		this.sourceNet = sourceNet;
	}
	public void setDBName(String dBName) {
		this.dBName = dBName;
	}
	public void setHost(String host) {
		this.host = host;
	}
}
